# _*_coding:utf-8 _*_
#@Time    :2019/12/16 12:00
#@Author  :Dapan
#@Email : 248312738@qq.com
import pymysql


class DB():
    """链接内网mysql 用于保存查询操作记录"""
    def __init__(self):
        # 打开数据库连接
        self.db = pymysql.connect(
            host="127.0.0.1",
            port=3306,
            user="root",
            passwd="123456",
            db="ztc_sys_db",
            charset='utf8')
        # 通过cursor方法获取操作游标
        self.cursor = self.db.cursor()


        # sql语句
        self.sql_username = "SELECT username FROM account_info WHERE del_status=0;"
        self.sql_password = "SELECT password FROM account_info WHERE username='%s';"
        self.sql_insert_user_info = "insert into account_info(username,password,del_status) values(%s, %s, %s)"
        self.sql_modify_del_status = "update account_info set del_status='%s' where username='%s'"
        self.sql_delete_account_info = "delete from account_info where username='%s';"

        self.sql_insert_log_info = "insert into action_log(username,wangwang,action_type,plan_name,goods_name,crowd_name_list) values('%s','%s','%s','%s','%s','%s');"
        self.sql_log_info = "SELECT * FROM action_log WHERE %s='%s';"


    def query_field(self, sql):
        """查询相关字段信息"""
        self.cursor.execute(sql)
        data_list = []
        for item in self.cursor.fetchall():
            data_list.append(item[0])

        return data_list

    def query_all_username(self):
        return self.query_field(self.sql_username)

    def query_password(self,username):
        """根据用户名查询对应的密码"""
        self.cursor.execute(self.sql_password % username)
        # 使用fetchone方法获取一个查询结果集
        data = self.cursor.fetchall()  # fetch:获取
        return data[0][0]

    def insert_user_info(self,username,password,del_status=0):
        self.cursor.execute(self.sql_insert_user_info, [username, password,del_status])  # 列表格式数据
        self.db.commit()

    def delete_user_info_logic(self,username):
        """逻辑删除，修改删除状态"""
        self.cursor.execute(self.sql_modify_del_status%(1,username))
        self.db.commit()


    def delete_user_info(self,username):
        """删除"""
        self.cursor.execute(self.sql_delete_account_info%username)
        self.db.commit()

    def insert_log_info(self,username,wangwang,action_type,plan_name,goods_name,crowd_name_list):
        """插入日志信息"""
        self.cursor.execute(self.sql_insert_log_info%(username,wangwang,action_type,plan_name,goods_name,crowd_name_list))
        self.db.commit()


    def query_log_info(self,condition,value):
        """
        查询操作日志数据
        condition: 查询条件名称,日志表中的字段名
        value： 查询条件
        """
        self.cursor.execute(self.sql_log_info % (condition,value))
        data = self.cursor.fetchall()  # fetch:获取
        return data

    def __del__(self):
        """"""
        # 关闭数据库连接
        self.db.close()





if __name__ == '__main__':
    a = DB()
    # a.insert_user_info('Dapan','123')
    # a.delete_user_info('Dapan')
    # a.delete_user_info_logic('Dapan')
    # print(a.query_all_username())
    # print(a.query_password('Dapan'))
    # username='Dapan'
    # wangwang="qw"
    # action_type=1
    # plan_name="uhdas"
    # goods_name="ska"
    # crowd_name_list='ajshkd'
    # a.insert_log_info(username,wangwang,action_type,plan_name,goods_name,crowd_name_list)
    print(a.query_log_info('wangwang', 'qw'))



